import os

import pandas as pd
from zc_core.client.mongo_client import Mongo
from scrapy.utils.project import get_project_settings

meta_map = [
    {'col': '_id', 'title': '商品编号'},
    {'col': 'skuName', 'title': '商品名称'},
    {'col': 'unit', 'title': '单位'},
    {'col': 'salePrice', 'title': '销售价'},
    {'col': 'originPrice', 'title': '原价'},
    {'col': 'brandName', 'title': '品牌'},
    {'col': 'brandModel', 'title': '型号'},
    {'col': 'catalog1Name', 'title': '一级分类'},
    {'col': 'catalog2Name', 'title': '二级分类'},
    {'col': 'catalog3Name', 'title': '三级分类'},
    {'col': 'supplierName', 'title': '供应商名称'},
    {'col': 'supplierSkuId', 'title': '供应商商品编号'},
    {'col': 'soldCount', 'title': '累计销售额'},
    {'col': 'skuImg', 'title': '首图链接'},
    {'col': 'stock', 'title': "库存"}
]


def export_data(table, file, query={}):
    # print(list(Mongo().db.data_20210529.find({"brandModel":{"$ne":None},"catalog1Name":{"$in":["办公用品"]}})))
    # 结构组装
    columns = list()
    headers = list()
    for meta in meta_map:
        columns.append(meta.get('col'))
        headers.append(meta.get('title'))

    # 数据查询
    rows = Mongo().list(
        collection=table,
        fields=columns,
        query=query,
        sort=[]
    )

    # print(rows)
    # 数据转换
    # for row in rows:
    #     row['publishTime'] = format_time(row.pop('publishTime'))
    #     row['deadlineTime'] = format_time(row.pop('deadlineTime'))
    #     row['validityDate'] = format_time(row.pop('validityDate'))

    # 写入Excel
    if rows:
        write = pd.ExcelWriter(file)
        df = pd.DataFrame(rows)
        df.to_excel(write, sheet_name='数据', columns=columns, header=headers, index=False, encoding='utf-8')
        write.save()
        print('导出成功~')
    else:
        print('无数据~')


if __name__ == '__main__':
    bot_name = get_project_settings().get('BOT_NAME')
    batch_no = '20210826'
    dir = f'/work/{bot_name}'
    if not os.path.exists(dir):
        os.makedirs(dir)
    table = f'data_{batch_no}'
    file = f'{dir}/阳光七采_{batch_no}.xlsx'
    print(f'{table}, {file}')
    export_data(
        table=table,
        file=file,
        query={
            'catalog2Name': {'$in': ['打印机(打复印设备)', '打复印设备耗材', '复印机']}
        },
    )
